<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>MySQL学习第二天 | 凌歆的博客</title>
    <meta name="generator" content="VuePress 1.9.5">
    <link rel="icon" href="/img/favicon.ico">
    <meta name="description" content="web前端技术博客,专注web前端学习与总结。JavaScript,js,ES6,TypeScript,vue,React,python,css3,html5,Node,git,github等技术文章。">
    <meta name="keywords" content="前端博客,个人技术博客,前端,前端开发,前端框架,web前端,前端面试题,技术文档,学习,面试,JavaScript,js,ES6,TypeScript,vue,python,css3,html5,Node,git,github,markdown">
    <meta name="baidu-site-verification" content="7F55weZDDc">
    <meta name="theme-color" content="#11a8cd">
    
    <link rel="preload" href="/assets/css/0.styles.d9c8cf8c.css" as="style"><link rel="preload" href="/assets/js/app.57550e13.js" as="script"><link rel="preload" href="/assets/js/2.f014c35f.js" as="script"><link rel="preload" href="/assets/js/92.1c28035b.js" as="script"><link rel="prefetch" href="/assets/js/10.03a98811.js"><link rel="prefetch" href="/assets/js/100.145e708e.js"><link rel="prefetch" href="/assets/js/101.098ca5c8.js"><link rel="prefetch" href="/assets/js/102.4d45c69a.js"><link rel="prefetch" href="/assets/js/103.499de505.js"><link rel="prefetch" href="/assets/js/104.c2c1b9b6.js"><link rel="prefetch" href="/assets/js/105.4e9b0663.js"><link rel="prefetch" href="/assets/js/106.9bb8cf6f.js"><link rel="prefetch" href="/assets/js/107.a81b32d7.js"><link rel="prefetch" href="/assets/js/108.81511e67.js"><link rel="prefetch" href="/assets/js/109.6a6741bf.js"><link rel="prefetch" href="/assets/js/11.e8729182.js"><link rel="prefetch" href="/assets/js/110.de657fe7.js"><link rel="prefetch" href="/assets/js/111.b092af6d.js"><link rel="prefetch" href="/assets/js/112.2dca5709.js"><link rel="prefetch" href="/assets/js/113.dbed1fac.js"><link rel="prefetch" href="/assets/js/114.e95a0131.js"><link rel="prefetch" href="/assets/js/115.2e3e8285.js"><link rel="prefetch" href="/assets/js/116.e64f7fec.js"><link rel="prefetch" href="/assets/js/117.583552c9.js"><link rel="prefetch" href="/assets/js/118.aa5d18f0.js"><link rel="prefetch" href="/assets/js/119.0c7b52d7.js"><link rel="prefetch" href="/assets/js/12.d63acec2.js"><link rel="prefetch" href="/assets/js/120.13f86281.js"><link rel="prefetch" href="/assets/js/121.e2b5b7f9.js"><link rel="prefetch" href="/assets/js/122.5252216a.js"><link rel="prefetch" href="/assets/js/123.8b8a3503.js"><link rel="prefetch" href="/assets/js/124.60df855c.js"><link rel="prefetch" href="/assets/js/125.f073ab1c.js"><link rel="prefetch" href="/assets/js/126.7b73de6a.js"><link rel="prefetch" href="/assets/js/127.4e25971c.js"><link rel="prefetch" href="/assets/js/128.0edf0e06.js"><link rel="prefetch" href="/assets/js/129.5cb5a70a.js"><link rel="prefetch" href="/assets/js/13.2259ef71.js"><link rel="prefetch" href="/assets/js/130.00247fda.js"><link rel="prefetch" href="/assets/js/131.d6fc003a.js"><link rel="prefetch" href="/assets/js/132.1a32b18f.js"><link rel="prefetch" href="/assets/js/133.e46c0193.js"><link rel="prefetch" href="/assets/js/134.a6ad681f.js"><link rel="prefetch" href="/assets/js/135.2fcbe137.js"><link rel="prefetch" href="/assets/js/136.f69bf451.js"><link rel="prefetch" href="/assets/js/137.b9ef2fcc.js"><link rel="prefetch" href="/assets/js/138.7fdd9feb.js"><link rel="prefetch" href="/assets/js/139.a3a37c91.js"><link rel="prefetch" href="/assets/js/14.0d6b23b9.js"><link rel="prefetch" href="/assets/js/140.a7c013ae.js"><link rel="prefetch" href="/assets/js/141.956c36ce.js"><link rel="prefetch" href="/assets/js/142.5aacfe42.js"><link rel="prefetch" href="/assets/js/143.57d691ef.js"><link rel="prefetch" href="/assets/js/144.b1e5766e.js"><link rel="prefetch" href="/assets/js/145.463284b7.js"><link rel="prefetch" href="/assets/js/146.6cc6420c.js"><link rel="prefetch" href="/assets/js/147.5b78c5a2.js"><link rel="prefetch" href="/assets/js/148.8d1d7679.js"><link rel="prefetch" href="/assets/js/149.4e0eb213.js"><link rel="prefetch" href="/assets/js/15.f6fde69d.js"><link rel="prefetch" href="/assets/js/150.b183de37.js"><link rel="prefetch" href="/assets/js/151.729ae770.js"><link rel="prefetch" href="/assets/js/152.b1297018.js"><link rel="prefetch" href="/assets/js/153.bb991bca.js"><link rel="prefetch" href="/assets/js/154.6f2286aa.js"><link rel="prefetch" href="/assets/js/155.6a9c7e4d.js"><link rel="prefetch" href="/assets/js/156.f7bd535d.js"><link rel="prefetch" href="/assets/js/157.64c0065c.js"><link rel="prefetch" href="/assets/js/158.d31ae949.js"><link rel="prefetch" href="/assets/js/159.7daea8a7.js"><link rel="prefetch" href="/assets/js/16.7be21beb.js"><link rel="prefetch" href="/assets/js/160.33cc971a.js"><link rel="prefetch" href="/assets/js/161.1676442a.js"><link rel="prefetch" href="/assets/js/162.71378046.js"><link rel="prefetch" href="/assets/js/163.99e49959.js"><link rel="prefetch" href="/assets/js/164.306f07d2.js"><link rel="prefetch" href="/assets/js/165.170977c7.js"><link rel="prefetch" href="/assets/js/166.f8602525.js"><link rel="prefetch" href="/assets/js/167.eea5c001.js"><link rel="prefetch" href="/assets/js/168.0146a311.js"><link rel="prefetch" href="/assets/js/169.05f9c9d9.js"><link rel="prefetch" href="/assets/js/17.2543f471.js"><link rel="prefetch" href="/assets/js/170.25d602b2.js"><link rel="prefetch" href="/assets/js/171.1ada26a6.js"><link rel="prefetch" href="/assets/js/172.2a0f697c.js"><link rel="prefetch" href="/assets/js/173.230d8ca8.js"><link rel="prefetch" href="/assets/js/174.e3758a2b.js"><link rel="prefetch" href="/assets/js/175.0f19f8ea.js"><link rel="prefetch" href="/assets/js/176.bf70d37d.js"><link rel="prefetch" href="/assets/js/177.4400f272.js"><link rel="prefetch" href="/assets/js/178.85af7b89.js"><link rel="prefetch" href="/assets/js/179.4cfaaaa2.js"><link rel="prefetch" href="/assets/js/18.394415f8.js"><link rel="prefetch" href="/assets/js/180.449c8409.js"><link rel="prefetch" href="/assets/js/181.73276924.js"><link rel="prefetch" href="/assets/js/182.5fab25cc.js"><link rel="prefetch" href="/assets/js/183.ecd4934d.js"><link rel="prefetch" href="/assets/js/184.246092cf.js"><link rel="prefetch" href="/assets/js/185.c671cd86.js"><link rel="prefetch" href="/assets/js/186.49bd1963.js"><link rel="prefetch" href="/assets/js/187.1fb32764.js"><link rel="prefetch" href="/assets/js/188.7c6885e9.js"><link rel="prefetch" href="/assets/js/189.a8701dec.js"><link rel="prefetch" href="/assets/js/19.ca7db8ea.js"><link rel="prefetch" href="/assets/js/190.ae3d3bde.js"><link rel="prefetch" href="/assets/js/191.ad7cd44e.js"><link rel="prefetch" href="/assets/js/192.bc443842.js"><link rel="prefetch" href="/assets/js/193.e4755764.js"><link rel="prefetch" href="/assets/js/194.814112ce.js"><link rel="prefetch" href="/assets/js/195.1c0aaa80.js"><link rel="prefetch" href="/assets/js/196.a6bd7add.js"><link rel="prefetch" href="/assets/js/20.4e91cd31.js"><link rel="prefetch" href="/assets/js/21.f095e6e1.js"><link rel="prefetch" href="/assets/js/22.72f4b8ab.js"><link rel="prefetch" href="/assets/js/23.e94eb9d2.js"><link rel="prefetch" href="/assets/js/24.0eae7d6f.js"><link rel="prefetch" href="/assets/js/25.36157609.js"><link rel="prefetch" href="/assets/js/26.dc5b6cba.js"><link rel="prefetch" href="/assets/js/27.c19b7b63.js"><link rel="prefetch" href="/assets/js/28.3aceae59.js"><link rel="prefetch" href="/assets/js/29.536091b3.js"><link rel="prefetch" href="/assets/js/3.d11119c4.js"><link rel="prefetch" href="/assets/js/30.821a4a30.js"><link rel="prefetch" href="/assets/js/31.5238784c.js"><link rel="prefetch" href="/assets/js/32.10bc7179.js"><link rel="prefetch" href="/assets/js/33.c1ed69c9.js"><link rel="prefetch" href="/assets/js/34.8d384231.js"><link rel="prefetch" href="/assets/js/35.d8890e52.js"><link rel="prefetch" href="/assets/js/36.eca37ad5.js"><link rel="prefetch" href="/assets/js/37.802549e3.js"><link rel="prefetch" href="/assets/js/38.53841770.js"><link rel="prefetch" href="/assets/js/39.7ae44409.js"><link rel="prefetch" href="/assets/js/4.493b9bca.js"><link rel="prefetch" href="/assets/js/40.ce56364a.js"><link rel="prefetch" href="/assets/js/41.85f0114b.js"><link rel="prefetch" href="/assets/js/42.714da6e2.js"><link rel="prefetch" href="/assets/js/43.fee4437c.js"><link rel="prefetch" href="/assets/js/44.b039b68b.js"><link rel="prefetch" href="/assets/js/45.96a55605.js"><link rel="prefetch" href="/assets/js/46.956ffb03.js"><link rel="prefetch" href="/assets/js/47.147d9218.js"><link rel="prefetch" href="/assets/js/48.ff787b40.js"><link rel="prefetch" href="/assets/js/49.871eee87.js"><link rel="prefetch" href="/assets/js/5.e0ba07d7.js"><link rel="prefetch" href="/assets/js/50.99b7c29e.js"><link rel="prefetch" href="/assets/js/51.8ecbf4ba.js"><link rel="prefetch" href="/assets/js/52.53d91d7e.js"><link rel="prefetch" href="/assets/js/53.af8a0d2a.js"><link rel="prefetch" href="/assets/js/54.a2f848b5.js"><link rel="prefetch" href="/assets/js/55.267e9947.js"><link rel="prefetch" href="/assets/js/56.0b201c40.js"><link rel="prefetch" href="/assets/js/57.cbdfd6f7.js"><link rel="prefetch" href="/assets/js/58.636c0c3f.js"><link rel="prefetch" href="/assets/js/59.fc9216c4.js"><link rel="prefetch" href="/assets/js/6.b98373f2.js"><link rel="prefetch" href="/assets/js/60.34f16fc4.js"><link rel="prefetch" href="/assets/js/61.4d130bd8.js"><link rel="prefetch" href="/assets/js/62.927caa10.js"><link rel="prefetch" href="/assets/js/63.a451fee0.js"><link rel="prefetch" href="/assets/js/64.32a5d47b.js"><link rel="prefetch" href="/assets/js/65.bc099429.js"><link rel="prefetch" href="/assets/js/66.3953050d.js"><link rel="prefetch" href="/assets/js/67.e934823b.js"><link rel="prefetch" href="/assets/js/68.4bcb52b0.js"><link rel="prefetch" href="/assets/js/69.bede18c8.js"><link rel="prefetch" href="/assets/js/7.8de8df2d.js"><link rel="prefetch" href="/assets/js/70.fce1daac.js"><link rel="prefetch" href="/assets/js/71.ea0a8c5d.js"><link rel="prefetch" href="/assets/js/72.15382c9e.js"><link rel="prefetch" href="/assets/js/73.848364d3.js"><link rel="prefetch" href="/assets/js/74.5fd08a50.js"><link rel="prefetch" href="/assets/js/75.c937c70d.js"><link rel="prefetch" href="/assets/js/76.a0dacd5a.js"><link rel="prefetch" href="/assets/js/77.4019cc23.js"><link rel="prefetch" href="/assets/js/78.9676a9e1.js"><link rel="prefetch" href="/assets/js/79.e46e10d5.js"><link rel="prefetch" href="/assets/js/8.2098eb96.js"><link rel="prefetch" href="/assets/js/80.987e83cb.js"><link rel="prefetch" href="/assets/js/81.eb98ff36.js"><link rel="prefetch" href="/assets/js/82.623561a7.js"><link rel="prefetch" href="/assets/js/83.e256d909.js"><link rel="prefetch" href="/assets/js/84.7d06a550.js"><link rel="prefetch" href="/assets/js/85.b8d9879e.js"><link rel="prefetch" href="/assets/js/86.31c4581b.js"><link rel="prefetch" href="/assets/js/87.e06ee05e.js"><link rel="prefetch" href="/assets/js/88.81ef718d.js"><link rel="prefetch" href="/assets/js/89.ba237d2f.js"><link rel="prefetch" href="/assets/js/9.36092fd0.js"><link rel="prefetch" href="/assets/js/90.ad2c9d92.js"><link rel="prefetch" href="/assets/js/91.a661f52d.js"><link rel="prefetch" href="/assets/js/93.c8b8e3d6.js"><link rel="prefetch" href="/assets/js/94.b6bc4a44.js"><link rel="prefetch" href="/assets/js/95.92cea882.js"><link rel="prefetch" href="/assets/js/96.5373491b.js"><link rel="prefetch" href="/assets/js/97.bb39efc8.js"><link rel="prefetch" href="/assets/js/98.af7a030f.js"><link rel="prefetch" href="/assets/js/99.07dc87d8.js">
    <link rel="stylesheet" href="/assets/css/0.styles.d9c8cf8c.css">
  </head>
  <body class="theme-mode-light">
    <div id="app" data-server-rendered="true"><div class="theme-container sidebar-open have-rightmenu"><header class="navbar blur"><div title="目录" class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/" class="home-link router-link-active"><img src="https://lingxin-tanhua.oss-cn-shanghai.aliyuncs.com/QQ%E6%88%AA%E5%9B%BE20220503092755.png" alt="凌歆的博客" class="logo"> <span class="site-name can-hide">凌歆的博客</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/" class="nav-link">首页</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端" class="dropdown-title"><a href="/web/" class="link-title">前端</a> <span class="title" style="display:none;">前端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>学习笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/note/html/" class="nav-link">HTML笔记</a></li><li class="dropdown-subitem"><a href="/note/js-low/" class="nav-link">JavaScript基础笔记</a></li><li class="dropdown-subitem"><a href="/note/js-high/" class="nav-link">JavaScript高阶笔记</a></li><li class="dropdown-subitem"><a href="/note/js-dom-bom/" class="nav-link">JavaScript DOM和BOM笔记</a></li><li class="dropdown-subitem"><a href="/note/jquery/" class="nav-link">jQuery笔记</a></li><li class="dropdown-subitem"><a href="/note/ajax/" class="nav-link">Ajax笔记</a></li><li class="dropdown-subitem"><a href="/note/nodejs/" class="nav-link">NodeJs笔记</a></li><li class="dropdown-subitem"><a href="/note/reg/" class="nav-link">正则表达式笔记</a></li><li class="dropdown-subitem"><a href="/note/vue/" class="nav-link">Vue笔记</a></li><li class="dropdown-subitem"><a href="/note/react/" class="nav-link">react笔记</a></li><li class="dropdown-subitem"><a href="/note/wx/" class="nav-link">微信小程序笔记</a></li><li class="dropdown-subitem"><a href="/note/uniapp/" class="nav-link">uniapp笔记</a></li><li class="dropdown-subitem"><a href="/note/echarts/" class="nav-link">echarts笔记</a></li><li class="dropdown-subitem"><a href="/note/git/" class="nav-link">Git笔记</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="后端" class="dropdown-title"><a href="/back/" class="link-title">后端</a> <span class="title" style="display:none;">后端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/note/java/" class="nav-link">JAVA</a></li><li class="dropdown-item"><!----> <a href="/note/javaweb/" class="nav-link">JavaWeb</a></li><li class="dropdown-item"><!----> <a href="/note/mysql/" class="nav-link">MySql</a></li><li class="dropdown-item"><!----> <a href="/note/ssm/" class="nav-link">ssm</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="算法" class="dropdown-title"><a href="/algor/" class="link-title">算法</a> <span class="title" style="display:none;">算法</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/note/sf/" class="nav-link">算法训练营</a></li><li class="dropdown-item"><!----> <a href="/note/js-sf/" class="nav-link">js数据结构与算法</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="面试" class="dropdown-title"><a href="/mianshi/" class="link-title">面试</a> <span class="title" style="display:none;">面试</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/note/lqb/" class="nav-link">蓝桥杯备战</a></li><li class="dropdown-item"><!----> <a href="/note/ms/" class="nav-link">前端面试</a></li><li class="dropdown-item"><!----> <a href="/note/hmms/" class="nav-link">黑马面试</a></li><li class="dropdown-item"><!----> <a href="/note/xzms/" class="nav-link">校招零距离面试</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="解决方案" class="dropdown-title"><a href="/resolve/" class="link-title">解决方案</a> <span class="title" style="display:none;">解决方案</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/note/qd-resolve/" class="nav-link">前端解决方案</a></li><li class="dropdown-item"><!----> <a href="/note/hd-resolve/" class="nav-link">后端解决方案</a></li></ul></div></div><div class="nav-item"><a href="/pages/beb6c0bd8a66cea6/" class="nav-link">收藏</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/archives/" class="nav-link">归档</a></li></ul></div></div> <a href="https://github.com/linxin1123/vuepress-blog" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav></div></header> <div class="sidebar-mask"></div> <div class="sidebar-hover-trigger"></div> <aside class="sidebar" style="display:none;"><div class="blogger"><img src="https://lingxin-tanhua.oss-cn-shanghai.aliyuncs.com/QQ%E6%88%AA%E5%9B%BE20220503092755.png"> <div class="blogger-info"><h3>lingxin</h3> <span>凌歆</span></div></div> <nav class="nav-links"><div class="nav-item"><a href="/" class="nav-link">首页</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端" class="dropdown-title"><a href="/web/" class="link-title">前端</a> <span class="title" style="display:none;">前端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>学习笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/note/html/" class="nav-link">HTML笔记</a></li><li class="dropdown-subitem"><a href="/note/js-low/" class="nav-link">JavaScript基础笔记</a></li><li class="dropdown-subitem"><a href="/note/js-high/" class="nav-link">JavaScript高阶笔记</a></li><li class="dropdown-subitem"><a href="/note/js-dom-bom/" class="nav-link">JavaScript DOM和BOM笔记</a></li><li class="dropdown-subitem"><a href="/note/jquery/" class="nav-link">jQuery笔记</a></li><li class="dropdown-subitem"><a href="/note/ajax/" class="nav-link">Ajax笔记</a></li><li class="dropdown-subitem"><a href="/note/nodejs/" class="nav-link">NodeJs笔记</a></li><li class="dropdown-subitem"><a href="/note/reg/" class="nav-link">正则表达式笔记</a></li><li class="dropdown-subitem"><a href="/note/vue/" class="nav-link">Vue笔记</a></li><li class="dropdown-subitem"><a href="/note/react/" class="nav-link">react笔记</a></li><li class="dropdown-subitem"><a href="/note/wx/" class="nav-link">微信小程序笔记</a></li><li class="dropdown-subitem"><a href="/note/uniapp/" class="nav-link">uniapp笔记</a></li><li class="dropdown-subitem"><a href="/note/echarts/" class="nav-link">echarts笔记</a></li><li class="dropdown-subitem"><a href="/note/git/" class="nav-link">Git笔记</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="后端" class="dropdown-title"><a href="/back/" class="link-title">后端</a> <span class="title" style="display:none;">后端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/note/java/" class="nav-link">JAVA</a></li><li class="dropdown-item"><!----> <a href="/note/javaweb/" class="nav-link">JavaWeb</a></li><li class="dropdown-item"><!----> <a href="/note/mysql/" class="nav-link">MySql</a></li><li class="dropdown-item"><!----> <a href="/note/ssm/" class="nav-link">ssm</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="算法" class="dropdown-title"><a href="/algor/" class="link-title">算法</a> <span class="title" style="display:none;">算法</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/note/sf/" class="nav-link">算法训练营</a></li><li class="dropdown-item"><!----> <a href="/note/js-sf/" class="nav-link">js数据结构与算法</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="面试" class="dropdown-title"><a href="/mianshi/" class="link-title">面试</a> <span class="title" style="display:none;">面试</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/note/lqb/" class="nav-link">蓝桥杯备战</a></li><li class="dropdown-item"><!----> <a href="/note/ms/" class="nav-link">前端面试</a></li><li class="dropdown-item"><!----> <a href="/note/hmms/" class="nav-link">黑马面试</a></li><li class="dropdown-item"><!----> <a href="/note/xzms/" class="nav-link">校招零距离面试</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="解决方案" class="dropdown-title"><a href="/resolve/" class="link-title">解决方案</a> <span class="title" style="display:none;">解决方案</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/note/qd-resolve/" class="nav-link">前端解决方案</a></li><li class="dropdown-item"><!----> <a href="/note/hd-resolve/" class="nav-link">后端解决方案</a></li></ul></div></div><div class="nav-item"><a href="/pages/beb6c0bd8a66cea6/" class="nav-link">收藏</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/archives/" class="nav-link">归档</a></li></ul></div></div> <a href="https://github.com/linxin1123/vuepress-blog" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav>  <ul class="sidebar-links"><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>学习笔记</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>Java笔记</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>JavaWeb笔记</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading open"><span>MySql笔记</span> <span class="arrow down"></span></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/pages/533d88/" class="sidebar-link">mysql学习第一天</a></li><li><a href="/pages/e13f8c/" aria-current="page" class="active sidebar-link">MySQL学习第二天</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level2"><a href="/pages/e13f8c/#_1-dql-后续" class="sidebar-link">1. DQL(后续)</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/e13f8c/#_1-排序查询" class="sidebar-link">1. 排序查询</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/e13f8c/#_2-聚合函数" class="sidebar-link">2. 聚合函数</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/e13f8c/#_2-1-解决排除null值计算的问题" class="sidebar-link">2.1 解决排除null值计算的问题</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/e13f8c/#_3-分组查询" class="sidebar-link">3. 分组查询</a></li><li class="sidebar-sub-header level2"><a href="/pages/e13f8c/#_4-分页查询" class="sidebar-link">4. 分页查询</a></li><li class="sidebar-sub-header level2"><a href="/pages/e13f8c/#_5-约束" class="sidebar-link">5. 约束</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/e13f8c/#_5-1-非空约束-not-null" class="sidebar-link">5.1 非空约束（not null）</a></li><li class="sidebar-sub-header level4"><a href="/pages/e13f8c/#_5-1-1-删除表的非空约束" class="sidebar-link">5.1.1 删除表的非空约束</a></li><li class="sidebar-sub-header level4"><a href="/pages/e13f8c/#_5-1-2-添加表的非空约束" class="sidebar-link">5.1.2 添加表的非空约束</a></li><li class="sidebar-sub-header level3"><a href="/pages/e13f8c/#_5-2-唯一约束-unique" class="sidebar-link">5.2 唯一约束(unique)</a></li><li class="sidebar-sub-header level4"><a href="/pages/e13f8c/#注意点" class="sidebar-link">注意点</a></li><li class="sidebar-sub-header level4"><a href="/pages/e13f8c/#_5-2-1-在创建表的时候添加唯一性约束" class="sidebar-link">5.2.1 在创建表的时候添加唯一性约束</a></li><li class="sidebar-sub-header level4"><a href="/pages/e13f8c/#_5-2-2-删除表的唯一性约束" class="sidebar-link">5.2.2 删除表的唯一性约束</a></li><li class="sidebar-sub-header level4"><a href="/pages/e13f8c/#_5-2-3-添加表的唯一性约束" class="sidebar-link">5.2.3 添加表的唯一性约束</a></li><li class="sidebar-sub-header level3"><a href="/pages/e13f8c/#_5-3-主键约束-primary-key" class="sidebar-link">5.3 主键约束(primary key)</a></li><li class="sidebar-sub-header level4"><a href="/pages/e13f8c/#_5-3-1-主键约束的概念" class="sidebar-link">5.3.1 主键约束的概念</a></li><li class="sidebar-sub-header level4"><a href="/pages/e13f8c/#_5-3-2-在创建表的时候添加主键约束" class="sidebar-link">5.3.2 在创建表的时候添加主键约束</a></li><li class="sidebar-sub-header level4"><a href="/pages/e13f8c/#_5-3-3-删除主键约束" class="sidebar-link">5.3.3 删除主键约束</a></li><li class="sidebar-sub-header level4"><a href="/pages/e13f8c/#_5-3-4-添加主键约束" class="sidebar-link">5.3.4 添加主键约束</a></li><li class="sidebar-sub-header level4"><a href="/pages/e13f8c/#_5-3-5-主键约束-自动增长" class="sidebar-link">5.3.5 主键约束_自动增长</a></li><li class="sidebar-sub-header level5"><a href="/pages/e13f8c/#注意点-2" class="sidebar-link">注意点</a></li><li class="sidebar-sub-header level5"><a href="/pages/e13f8c/#删除自动增长" class="sidebar-link">删除自动增长</a></li><li class="sidebar-sub-header level5"><a href="/pages/e13f8c/#添加自动增长" class="sidebar-link">添加自动增长</a></li><li class="sidebar-sub-header level3"><a href="/pages/e13f8c/#_5-4-外键约束" class="sidebar-link">5.4 外键约束</a></li><li class="sidebar-sub-header level4"><a href="/pages/e13f8c/#解决方案1-实际上不能解决-看似可以-两张表之间并没有关联" class="sidebar-link">解决方案1(实际上不能解决，看似可以,两张表之间并没有关联)</a></li><li class="sidebar-sub-header level4"><a href="/pages/e13f8c/#_5-4-1-外键约束的语法" class="sidebar-link">5.4.1 外键约束的语法</a></li><li class="sidebar-sub-header level5"><a href="/pages/e13f8c/#从表-多方-被别人约束的表-使用别人的数据-存在外键" class="sidebar-link">从表（多方，被别人约束的表，使用别人的数据（存在外键））</a></li><li class="sidebar-sub-header level5"><a href="/pages/e13f8c/#主表-一方-用来约束别人的表" class="sidebar-link">主表（一方，用来约束别人的表）</a></li><li class="sidebar-sub-header level5"><a href="/pages/e13f8c/#添加记录" class="sidebar-link">添加记录</a></li><li class="sidebar-sub-header level4"><a href="/pages/e13f8c/#_5-4-2-删除外键" class="sidebar-link">5.4.2 删除外键</a></li><li class="sidebar-sub-header level4"><a href="/pages/e13f8c/#_5-4-3-添加外键" class="sidebar-link">5.4.3 添加外键</a></li><li class="sidebar-sub-header level4"><a href="/pages/e13f8c/#_5-4-4-添加级联操作" class="sidebar-link">5.4.4 添加级联操作</a></li><li class="sidebar-sub-header level5"><a href="/pages/e13f8c/#级联更新-on-update-cascade" class="sidebar-link">级联更新（on update cascade）</a></li><li class="sidebar-sub-header level5"><a href="/pages/e13f8c/#级联删除" class="sidebar-link">级联删除</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/e13f8c/#_6-表与表之间的关系" class="sidebar-link">6. 表与表之间的关系</a></li><li class="sidebar-sub-header level2"><a href="/pages/e13f8c/#_7-一个旅游案例" class="sidebar-link">7.一个旅游案例</a></li><li class="sidebar-sub-header level2"><a href="/pages/e13f8c/#_8-数据库范式" class="sidebar-link">8.数据库范式</a></li><li class="sidebar-sub-header level2"><a href="/pages/e13f8c/#_9-数据库的备份与还原" class="sidebar-link">9.数据库的备份与还原</a></li></ul></li><li><a href="/pages/774997/" class="sidebar-link">MySql学习第三天</a></li></ul></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>SSM笔记</span> <span class="arrow right"></span></p> <!----></section></li></ul> </aside> <div><main class="page"><div class="theme-vdoing-wrapper "><div class="articleInfo-wrap" data-v-06225672><div class="articleInfo" data-v-06225672><ul class="breadcrumbs" data-v-06225672><li data-v-06225672><a href="/" title="首页" class="iconfont icon-home router-link-active" data-v-06225672></a></li> <li data-v-06225672><a href="/back/#后端" data-v-06225672>后端</a></li><li data-v-06225672><a href="/back/#MySql笔记" data-v-06225672>MySql笔记</a></li></ul> <div class="info" data-v-06225672><div title="作者" class="author iconfont icon-touxiang" data-v-06225672><a href="https://github.com/linxin1123" target="_blank" title="作者" class="beLink" data-v-06225672>lingxin</a></div> <div title="创建时间" class="date iconfont icon-riqi" data-v-06225672><a href="javascript:;" data-v-06225672>2023-01-25</a></div> <!----></div></div></div> <!----> <div class="content-wrapper"><div class="right-menu-wrapper"><div class="right-menu-margin"><div class="right-menu-title">目录</div> <div class="right-menu-content"></div></div></div> <h1><img src="">MySQL学习第二天<!----></h1>  <div class="theme-vdoing-content content__default"><h1 id="mysql-学习第二天"><a href="#mysql-学习第二天" class="header-anchor">#</a> MySQL 学习第二天</h1> <p>@[toc]</p> <h2 id="_1-dql-后续"><a href="#_1-dql-后续" class="header-anchor">#</a> 1. DQL(后续)</h2> <ul><li><strong>排序查询</strong></li> <li><strong>聚合函数</strong></li> <li><strong>分组查询</strong></li> <li><strong>分页查询</strong></li></ul> <h3 id="_1-排序查询"><a href="#_1-排序查询" class="header-anchor">#</a> 1. 排序查询</h3> <ul><li>语法</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">order</span> <span class="token keyword">by</span> 字段<span class="token number">1</span> 排序方式<span class="token number">1</span><span class="token punctuation">,</span>字段<span class="token number">2</span> 排序方式<span class="token number">2.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li><p>排序方式</p> <ul><li><strong>升序：ASC(默认)</strong></li> <li><strong>降序：DESC</strong></li></ul></li> <li><p>升序（默认）、
<img src="https://img-blog.csdnimg.cn/5410fb4b8dc142dea30960f77516167d.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p></li> <li><p>降序</p></li></ul> <p><img src="https://img-blog.csdnimg.cn/249353fe2c024cf0a2228b3f29d1b1d9.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <ul><li><strong>升序排列，第一排序条件（math）相同时，按第二排序（english）条件，即数学成绩相同时，再按英语成绩排序</strong></li></ul> <p><img src="https://img-blog.csdnimg.cn/b1d457f191034697b67642fac2ac14bc.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <h2 id="_2-聚合函数"><a href="#_2-聚合函数" class="header-anchor">#</a> 2. 聚合函数</h2> <ul><li><p>将一列数据作为一个整体，进行纵向的计算。</p> <ul><li>count：计算个数</li> <li>max：计算最大值</li> <li>min：计算最小值</li> <li>sum：计算和</li> <li>avg：计算平均值</li></ul></li> <li><p><strong>聚合函数的计算排除了null值</strong></p></li> <li><p>计算前的表信息</p></li></ul> <p><img src="https://img-blog.csdnimg.cn/32b8bb0444244cf8b25c7e0fded17794.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <ul><li><strong>count函数计算（排除null值）english</strong></li></ul> <p><img src="https://img-blog.csdnimg.cn/e2575c0934a249baa138ae5559d4d528.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <ul><li><strong>不排除null值(没有null值的一列)</strong></li></ul> <p><img src="https://img-blog.csdnimg.cn/eb0b7e2cd9f545e7a24f02e9e807472a.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <h3 id="_2-1-解决排除null值计算的问题"><a href="#_2-1-解决排除null值计算的问题" class="header-anchor">#</a> 2.1 解决排除null值计算的问题</h3> <ul><li><strong>选择不包含null值的一列</strong></li> <li><strong>使用 ifnull 函数</strong></li> <li><strong>count(*)不推荐</strong></li></ul> <p><img src="https://img-blog.csdnimg.cn/e226fba3d36343fbbd47e9819e951153.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <ul><li>count（*）</li></ul> <p><img src="https://img-blog.csdnimg.cn/2e8b610b55954dbda2e0a30866f25682.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <ul><li><p>max()
<img src="https://img-blog.csdnimg.cn/068f547ab76549f199d30d4426a34c55.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p></li> <li><p>min()</p></li></ul> <p><img src="https://img-blog.csdnimg.cn/83b54ddf877a4bbaa04f7af9021a4784.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <ul><li>sum()</li></ul> <p><img src="https://img-blog.csdnimg.cn/0c4e4a0970fe4937be2af271921fe70d.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <ul><li>avg()</li></ul> <p><img src="https://img-blog.csdnimg.cn/080c876261734fce84a4e4fa111f616e.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <h2 id="_3-分组查询"><a href="#_3-分组查询" class="header-anchor">#</a> 3. 分组查询</h2> <ul><li>语法</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">group</span> <span class="token keyword">by</span> 分组字段<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li><p>注意点</p> <ul><li><p><strong>分组之后的select的字段要么是分组字段，要么是聚合函数</strong></p></li> <li><p>where 和 having的区别</p> <ul><li><strong>where在分组之前进行限定，如果不满足条件，则不参与分组</strong></li> <li><strong>having在分组之后进行限定，如果不满足条件，则不会被查询出来</strong></li> <li><strong>where 后不可以跟聚合函数的判断，而having可以</strong></li></ul></li></ul></li> <li><p>示例：按性别进行分组，查询平均分和人数</p></li></ul> <p><img src="https://img-blog.csdnimg.cn/0246043f279b4c8899fcefa50e2f90b6.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <ul><li><strong>示例：按性别进行分组，但数学成绩低于70的不参与分组</strong></li></ul> <p><img src="https://img-blog.csdnimg.cn/a3952e8c56e14c0c880ad39eb02d973d.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <ul><li><strong>示例：having的使用</strong></li></ul> <p><img src="https://img-blog.csdnimg.cn/20e82efd468446a797adc284e13a8c0e.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <ul><li><strong>示例：给聚合函数起别名，便于判断</strong></li></ul> <p><img src="https://img-blog.csdnimg.cn/11de98f513b448bf897fff17328ab6a1.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <h2 id="_4-分页查询"><a href="#_4-分页查询" class="header-anchor">#</a> 4. 分页查询</h2> <ul><li><strong>语法（是一个MySQL的关键字，只能在MySQL中使用）</strong></li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">limit</span> 开始的索引<span class="token punctuation">,</span>每页查询的条数<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>一个公式</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code>开始的索引<span class="token operator">=</span>（当前的页码<span class="token operator">-</span><span class="token number">1</span>）<span class="token operator">*</span>每页显示的条数
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>示例</li></ul> <p><img src="https://img-blog.csdnimg.cn/df11b5ab132840d6a8befc25c04cac97.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <p><img src="https://img-blog.csdnimg.cn/dd6ad5f149374eb299b66acb7e942452.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <p><img src="https://img-blog.csdnimg.cn/4540e2e3826f47f0949d146a8d53c639.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <h2 id="_5-约束"><a href="#_5-约束" class="header-anchor">#</a> 5. 约束</h2> <ul><li><strong>概念：对表中的数据进行限定，保证数据的正确性，有效性和完整性</strong></li> <li>分类
<ul><li>主键约束：primary key</li> <li>非空约束：not null</li> <li>唯一约束：unique</li> <li>外键约束：foreign key</li></ul></li></ul> <h3 id="_5-1-非空约束-not-null"><a href="#_5-1-非空约束-not-null" class="header-anchor">#</a> 5.1 非空约束（not null）</h3> <ul><li>某一列的值不能为空</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 创建一个新的表</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> stu<span class="token punctuation">(</span>
	id <span class="token keyword">INT</span><span class="token punctuation">,</span>
	NAME <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token comment">-- 非空约束</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>



<span class="token keyword">SELECT</span> <span class="token operator">*</span><span class="token keyword">FROM</span> stu<span class="token punctuation">;</span>

<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> stu<span class="token punctuation">(</span>id<span class="token punctuation">,</span>NAME<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token string">'李华'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> stu<span class="token punctuation">(</span>id<span class="token punctuation">,</span>NAME<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br></div></div><ul><li>效果图</li></ul> <p><img src="https://img-blog.csdnimg.cn/89144cb48848465782c0a689b0927338.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <h4 id="_5-1-1-删除表的非空约束"><a href="#_5-1-1-删除表的非空约束" class="header-anchor">#</a> 5.1.1 删除表的非空约束</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 删除表的非空约束</span>

<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> stu <span class="token keyword">MODIFY</span> NAME <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><ul><li>效果图</li></ul> <p><img src="https://img-blog.csdnimg.cn/a2077f1ae4474b419d6633a5545037de.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <h4 id="_5-1-2-添加表的非空约束"><a href="#_5-1-2-添加表的非空约束" class="header-anchor">#</a> 5.1.2 添加表的非空约束</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 添加表的非空约束</span>

<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> stu <span class="token keyword">MODIFY</span> NAME <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h3 id="_5-2-唯一约束-unique"><a href="#_5-2-唯一约束-unique" class="header-anchor">#</a> 5.2 唯一约束(unique)</h3> <ul><li><strong>某一列的值不能重复</strong></li></ul> <h4 id="注意点"><a href="#注意点" class="header-anchor">#</a> 注意点</h4> <ul><li><strong>唯一性约束可以有null值，但是只能有一条记录为null</strong></li></ul> <h4 id="_5-2-1-在创建表的时候添加唯一性约束"><a href="#_5-2-1-在创建表的时候添加唯一性约束" class="header-anchor">#</a> 5.2.1 在创建表的时候添加唯一性约束</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 创建表示添加唯一约束</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> stu<span class="token punctuation">(</span>
	id <span class="token keyword">INT</span><span class="token punctuation">,</span>
	phoneNumber <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token keyword">UNIQUE</span> <span class="token comment">-- 唯一约束，手机号不能重复</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br></div></div><ul><li><strong>效果图：多次添加重复的手机号</strong></li></ul> <p><img src="https://img-blog.csdnimg.cn/9d4fe98970b44152beb112fb7fb50020.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <h4 id="_5-2-2-删除表的唯一性约束"><a href="#_5-2-2-删除表的唯一性约束" class="header-anchor">#</a> 5.2.2 删除表的唯一性约束</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 删除唯一性约束</span>

<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> stu <span class="token keyword">DROP</span> <span class="token keyword">INDEX</span> phoneNumber<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h4 id="_5-2-3-添加表的唯一性约束"><a href="#_5-2-3-添加表的唯一性约束" class="header-anchor">#</a> 5.2.3 添加表的唯一性约束</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 添加表的唯一性约束</span>

<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> stu <span class="token keyword">MODIFY</span> phoneNumber <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token keyword">UNIQUE</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h3 id="_5-3-主键约束-primary-key"><a href="#_5-3-主键约束-primary-key" class="header-anchor">#</a> 5.3 主键约束(primary key)</h3> <h4 id="_5-3-1-主键约束的概念"><a href="#_5-3-1-主键约束的概念" class="header-anchor">#</a> 5.3.1 主键约束的概念</h4> <ul><li><strong>非空且唯一</strong></li> <li><strong>主键就是表中记录的唯一标识</strong></li></ul> <h4 id="_5-3-2-在创建表的时候添加主键约束"><a href="#_5-3-2-在创建表的时候添加主键约束" class="header-anchor">#</a> 5.3.2 在创建表的时候添加主键约束</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> stu<span class="token punctuation">(</span>
	id <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span> <span class="token comment">-- 添加主键约束</span>
	NAME <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><ul><li><strong>唯一</strong></li></ul> <p><img src="https://img-blog.csdnimg.cn/1450f4613ca04afc80a15a3078ebec26.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <ul><li><strong>非空</strong></li></ul> <p><img src="https://img-blog.csdnimg.cn/cf8cec3f0eb648a388339206e4d02d23.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <h4 id="_5-3-3-删除主键约束"><a href="#_5-3-3-删除主键约束" class="header-anchor">#</a> 5.3.3 删除主键约束</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 删除主键</span>

<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> stu <span class="token keyword">DROP</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h4 id="_5-3-4-添加主键约束"><a href="#_5-3-4-添加主键约束" class="header-anchor">#</a> 5.3.4 添加主键约束</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 创建表后 添加主键</span>

<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> stu <span class="token keyword">MODIFY</span> id <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h4 id="_5-3-5-主键约束-自动增长"><a href="#_5-3-5-主键约束-自动增长" class="header-anchor">#</a> 5.3.5 主键约束_自动增长</h4> <ul><li><p><strong>概念：如果某一列是数值类型的，使用auto_increment 可以来完成值的自动增长（配合int类型的主键使用）</strong></p></li> <li><p>在创建表的时候添加自动增长</p></li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> stu<span class="token punctuation">(</span>
	id <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span> <span class="token comment">-- 添加主键约束和自动增长</span>
	NAME <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br></div></div><ul><li>插入数据，自动增长</li></ul> <p><img src="https://img-blog.csdnimg.cn/5618931704d9493486a8ad2ca3807c47.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <h5 id="注意点-2"><a href="#注意点-2" class="header-anchor">#</a> 注意点</h5> <ul><li><strong>自动增长是相当于上一条记录自动增长</strong></li></ul> <h5 id="删除自动增长"><a href="#删除自动增长" class="header-anchor">#</a> 删除自动增长</h5> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">alter</span> <span class="token keyword">table</span> stu <span class="token keyword">modify</span> id <span class="token keyword">int</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h5 id="添加自动增长"><a href="#添加自动增长" class="header-anchor">#</a> 添加自动增长</h5> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 添加自动增长</span>

<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> stu <span class="token keyword">MODIFY</span> id <span class="token keyword">INT</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h3 id="_5-4-外键约束"><a href="#_5-4-外键约束" class="header-anchor">#</a> 5.4 外键约束</h3> <ul><li>单表的缺点</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> emp <span class="token punctuation">(</span>
    id <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
    NAME <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    age <span class="token keyword">INT</span><span class="token punctuation">,</span>
    dep_name <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    dep_location <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">-- 添加数据</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp <span class="token punctuation">(</span>NAME<span class="token punctuation">,</span> age<span class="token punctuation">,</span> dep_name<span class="token punctuation">,</span> dep_location<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'张三'</span><span class="token punctuation">,</span> <span class="token number">20</span><span class="token punctuation">,</span> <span class="token string">'研发部'</span><span class="token punctuation">,</span> <span class="token string">'广州'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp <span class="token punctuation">(</span>NAME<span class="token punctuation">,</span> age<span class="token punctuation">,</span> dep_name<span class="token punctuation">,</span> dep_location<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'李四'</span><span class="token punctuation">,</span> <span class="token number">21</span><span class="token punctuation">,</span> <span class="token string">'研发部'</span><span class="token punctuation">,</span> <span class="token string">'广州'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp <span class="token punctuation">(</span>NAME<span class="token punctuation">,</span> age<span class="token punctuation">,</span> dep_name<span class="token punctuation">,</span> dep_location<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'王五'</span><span class="token punctuation">,</span> <span class="token number">20</span><span class="token punctuation">,</span> <span class="token string">'研发部'</span><span class="token punctuation">,</span> <span class="token string">'广州'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp <span class="token punctuation">(</span>NAME<span class="token punctuation">,</span> age<span class="token punctuation">,</span> dep_name<span class="token punctuation">,</span> dep_location<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'老王'</span><span class="token punctuation">,</span> <span class="token number">20</span><span class="token punctuation">,</span> <span class="token string">'销售部'</span><span class="token punctuation">,</span> <span class="token string">'深圳'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp <span class="token punctuation">(</span>NAME<span class="token punctuation">,</span> age<span class="token punctuation">,</span> dep_name<span class="token punctuation">,</span> dep_location<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'大王'</span><span class="token punctuation">,</span> <span class="token number">22</span><span class="token punctuation">,</span> <span class="token string">'销售部'</span><span class="token punctuation">,</span> <span class="token string">'深圳'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp <span class="token punctuation">(</span>NAME<span class="token punctuation">,</span> age<span class="token punctuation">,</span> dep_name<span class="token punctuation">,</span> dep_location<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'小王'</span><span class="token punctuation">,</span> <span class="token number">18</span><span class="token punctuation">,</span> <span class="token string">'销售部'</span><span class="token punctuation">,</span> <span class="token string">'深圳'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br></div></div><ul><li>效果图</li></ul> <p><img src="https://img-blog.csdnimg.cn/676002390e644f45900fbdbd3909ae44.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <ul><li><strong>缺点：数据冗余（研发部，广州）和（销售部，深圳）</strong></li></ul> <h4 id="解决方案1-实际上不能解决-看似可以-两张表之间并没有关联"><a href="#解决方案1-实际上不能解决-看似可以-两张表之间并没有关联" class="header-anchor">#</a> 解决方案1(实际上不能解决，看似可以,两张表之间并没有关联)</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 解决方案：分成 2 张表</span>
<span class="token comment">-- 创建部门表(id,dep_name,dep_location)</span>
<span class="token comment">-- 一方，主表</span>
<span class="token keyword">create</span> <span class="token keyword">table</span> department<span class="token punctuation">(</span>
    id <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span> <span class="token keyword">auto_increment</span><span class="token punctuation">,</span>
    dep_name <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    dep_location <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">-- 创建员工表(id,name,age,dep_id)</span>
<span class="token comment">-- 多方，从表</span>
<span class="token keyword">create</span> <span class="token keyword">table</span> employee<span class="token punctuation">(</span>
    id <span class="token keyword">int</span> <span class="token keyword">primary</span> <span class="token keyword">key</span> <span class="token keyword">auto_increment</span><span class="token punctuation">,</span>
    name <span class="token keyword">varchar</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    age <span class="token keyword">int</span><span class="token punctuation">,</span>
    dep_id <span class="token keyword">int</span> <span class="token comment">-- 外键对应主表的主键</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment">-- 添加 2 个部门</span>
<span class="token keyword">insert</span> <span class="token keyword">into</span> department <span class="token keyword">values</span><span class="token punctuation">(</span><span class="token boolean">null</span><span class="token punctuation">,</span> <span class="token string">'研发部'</span><span class="token punctuation">,</span><span class="token string">'广州'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token boolean">null</span><span class="token punctuation">,</span> <span class="token string">'销售部'</span><span class="token punctuation">,</span> <span class="token string">'深圳'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> department<span class="token punctuation">;</span>
<span class="token comment">-- 添加员工,dep_id 表示员工所在的部门</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employee <span class="token punctuation">(</span>NAME<span class="token punctuation">,</span> age<span class="token punctuation">,</span> dep_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'张三'</span><span class="token punctuation">,</span> <span class="token number">20</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span> 
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employee <span class="token punctuation">(</span>NAME<span class="token punctuation">,</span> age<span class="token punctuation">,</span> dep_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'李四'</span><span class="token punctuation">,</span> <span class="token number">21</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employee <span class="token punctuation">(</span>NAME<span class="token punctuation">,</span> age<span class="token punctuation">,</span> dep_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'王五'</span><span class="token punctuation">,</span> <span class="token number">20</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employee <span class="token punctuation">(</span>NAME<span class="token punctuation">,</span> age<span class="token punctuation">,</span> dep_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'老王'</span><span class="token punctuation">,</span> <span class="token number">20</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employee <span class="token punctuation">(</span>NAME<span class="token punctuation">,</span> age<span class="token punctuation">,</span> dep_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'大王'</span><span class="token punctuation">,</span> <span class="token number">22</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employee <span class="token punctuation">(</span>NAME<span class="token punctuation">,</span> age<span class="token punctuation">,</span> dep_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'小王'</span><span class="token punctuation">,</span> <span class="token number">18</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">select</span> <span class="token operator">*</span> <span class="token keyword">from</span> employee<span class="token punctuation">;</span>

</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br></div></div><h4 id="_5-4-1-外键约束的语法"><a href="#_5-4-1-外键约束的语法" class="header-anchor">#</a> 5.4.1 外键约束的语法</h4> <ul><li><p><strong>在创建表的时候添加外键约束，即主表和从表产生关联</strong></p></li> <li><p>语法</p></li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">constraint</span> 外键约束名称 <span class="token keyword">foreign</span> <span class="token keyword">key</span> <span class="token punctuation">(</span>外键字段名<span class="token punctuation">)</span> <span class="token keyword">references</span> 主表名<span class="token punctuation">(</span>主键字段名<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h5 id="从表-多方-被别人约束的表-使用别人的数据-存在外键"><a href="#从表-多方-被别人约束的表-使用别人的数据-存在外键" class="header-anchor">#</a> 从表（多方，被别人约束的表，使用别人的数据（存在外键））</h5> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code>
<span class="token comment">-- 员工从表，部门数据在部门表（主表）</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> employee<span class="token punctuation">(</span>
    id <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
    NAME <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    age <span class="token keyword">INT</span><span class="token punctuation">,</span>
    dep_id <span class="token keyword">INT</span><span class="token punctuation">,</span> <span class="token comment">-- 外键对应主表的主键</span>
    
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br></div></div><h5 id="主表-一方-用来约束别人的表"><a href="#主表-一方-用来约束别人的表" class="header-anchor">#</a> 主表（一方，用来约束别人的表）</h5> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 主表 部门表</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> department<span class="token punctuation">(</span>
    id <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
    dep_name <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
    dep_location <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br></div></div><h5 id="添加记录"><a href="#添加记录" class="header-anchor">#</a> 添加记录</h5> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 添加 2 个部门</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> department <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token string">'研发部'</span><span class="token punctuation">,</span><span class="token string">'广州'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token string">'销售部'</span><span class="token punctuation">,</span> <span class="token string">'深圳'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 添加员工,dep_id 表示员工所在的部门</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employee <span class="token punctuation">(</span>NAME<span class="token punctuation">,</span> age<span class="token punctuation">,</span> dep_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'张三'</span><span class="token punctuation">,</span> <span class="token number">20</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span> 
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employee <span class="token punctuation">(</span>NAME<span class="token punctuation">,</span> age<span class="token punctuation">,</span> dep_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'李四'</span><span class="token punctuation">,</span> <span class="token number">21</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employee <span class="token punctuation">(</span>NAME<span class="token punctuation">,</span> age<span class="token punctuation">,</span> dep_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'王五'</span><span class="token punctuation">,</span> <span class="token number">20</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employee <span class="token punctuation">(</span>NAME<span class="token punctuation">,</span> age<span class="token punctuation">,</span> dep_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'老王'</span><span class="token punctuation">,</span> <span class="token number">20</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employee <span class="token punctuation">(</span>NAME<span class="token punctuation">,</span> age<span class="token punctuation">,</span> dep_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'大王'</span><span class="token punctuation">,</span> <span class="token number">22</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> employee <span class="token punctuation">(</span>NAME<span class="token punctuation">,</span> age<span class="token punctuation">,</span> dep_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'小王'</span><span class="token punctuation">,</span> <span class="token number">18</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><ul><li>删除主表的id为1的记录错误效果图</li></ul> <p><img src="https://img-blog.csdnimg.cn/ea830219a5ad4cd0a7d85a9ddc75d0c6.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <h4 id="_5-4-2-删除外键"><a href="#_5-4-2-删除外键" class="header-anchor">#</a> 5.4.2 删除外键</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 删除外键</span>

<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> employee <span class="token keyword">DROP</span> <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> emp_dep_id<span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h4 id="_5-4-3-添加外键"><a href="#_5-4-3-添加外键" class="header-anchor">#</a> 5.4.3 添加外键</h4> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 添加外键</span>

<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> employee <span class="token keyword">ADD</span> <span class="token keyword">CONSTRAINT</span> emp_dep_id <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>dep_id<span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> department<span class="token punctuation">(</span>id<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><h4 id="_5-4-4-添加级联操作"><a href="#_5-4-4-添加级联操作" class="header-anchor">#</a> 5.4.4 添加级联操作</h4> <ul><li>概念：<strong>修改和删除主表的主键时，同时更新和删除从表的外键值</strong></li> <li>级联更新：<strong>修改主表的主键，从表的外键也改变</strong></li> <li>级联删除：<strong>删除主表的一个记录，从表的外键值是主表的主键值的记录被删除</strong></li></ul> <h5 id="级联更新-on-update-cascade"><a href="#级联更新-on-update-cascade" class="header-anchor">#</a> 级联更新（on update cascade）</h5> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CONSTRAINT</span> emp_dep_id <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>dep_id<span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> department<span class="token punctuation">(</span>id<span class="token punctuation">)</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CASCADE</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>效果图</li></ul> <p><img src="https://img-blog.csdnimg.cn/5ee4dd242bdb447fb3a4cffcc0f74edd.gif#pic_center" alt="在这里插入图片描述"></p> <h5 id="级联删除"><a href="#级联删除" class="header-anchor">#</a> 级联删除</h5> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CONSTRAINT</span> emp_dep_id <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>dep_id<span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> department<span class="token punctuation">(</span>id<span class="token punctuation">)</span> <span class="token keyword">ON</span> <span class="token keyword">UPDATE</span> <span class="token keyword">CASCADE</span> <span class="token keyword">ON</span> <span class="token keyword">DELETE</span> <span class="token keyword">CASCADE</span>  <span class="token comment">-- 这里不仅有级联更新，也有级联删除</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><h2 id="_6-表与表之间的关系"><a href="#_6-表与表之间的关系" class="header-anchor">#</a> 6. 表与表之间的关系</h2> <ol><li>多表之间的关系
<ol><li>分类：
<ol><li>一对一(了解)：
<ul><li>如：人和身份证</li> <li>分析：一个人只有一个身份证，一个身份证只能对应一个人</li></ul></li> <li>一对多(多对一)：
<ul><li>如：部门和员工</li> <li>分析：一个部门有多个员工，一个员工只能对应一个部门</li></ul></li> <li>多对多：
<ul><li>如：学生和课程</li> <li>分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择</li></ul></li></ol></li> <li>实现关系：
<ol><li><p><strong>一对多(多对一)：</strong></p> <ul><li>如：部门和员工</li> <li>实现方式：<strong>在多的一方建立外键，指向一的一方的主键。</strong></li> <li>图解</li></ul> <p><img src="https://img-blog.csdnimg.cn/6d055389bbed43a2ac4dcec52519cdea.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p></li> <li><p>多对多：</p> <ul><li>如：学生和课程</li> <li><strong>实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键</strong></li> <li>图解</li></ul> <p><img src="https://img-blog.csdnimg.cn/75390060ed324ca4b9d18892819a2d59.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p></li> <li><p>一对一(了解)：</p> <ul><li>如：人和身份证</li> <li>实现方式：一对一关系实现，可以在任意一方添加唯一外键指向另一方的主键。</li> <li>图解</li></ul> <p><img src="https://img-blog.csdnimg.cn/e3637761e58f4de583306b02d7a0cffe.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p></li></ol></li></ol></li></ol> <h2 id="_7-一个旅游案例"><a href="#_7-一个旅游案例" class="header-anchor">#</a> 7.一个旅游案例</h2> <p><img src="https://img-blog.csdnimg.cn/c6eef76de2f1427f8a16ccebb3ee32a2.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code>

<span class="token comment">-- 创建一个旅游分类表</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> cat_tours<span class="token punctuation">(</span>
	cid <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>  <span class="token comment">-- 旅游分类id</span>
	cname <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">UNIQUE</span>   <span class="token comment">-- 旅游分类的类名</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>


<span class="token comment">-- 旅游分类(1)和旅游线路（n）</span>

<span class="token comment">-- 旅游线路添加外键</span>

<span class="token comment">-- 创建一个旅游线路表</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> tours_routes<span class="token punctuation">(</span>
	rid <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span> <span class="token comment">-- 旅游线路的id</span>
	rname <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">UNIQUE</span><span class="token punctuation">,</span>   <span class="token comment">-- 旅游线路的名称</span>
	price <span class="token keyword">DOUBLE</span><span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span>        <span class="token comment">-- 旅游线路的价格</span>
	cid <span class="token keyword">INT</span><span class="token punctuation">,</span>                 <span class="token comment">-- 外键</span>
	<span class="token keyword">CONSTRAINT</span> rid_cid <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span>cid<span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> cat_tours<span class="token punctuation">(</span>cid<span class="token punctuation">)</span> <span class="token comment">-- 添加外键约束</span>
	
<span class="token punctuation">)</span><span class="token punctuation">;</span>


<span class="token comment">-- 创建一个用户表</span>

<span class="token comment">-- 用户（n）和旅游线路（m）</span>

<span class="token comment">-- 使用第3张表</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> users<span class="token punctuation">(</span>
	uid <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
	uname <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">UNIQUE</span><span class="token punctuation">,</span>
	psw <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span>
	
<span class="token punctuation">)</span><span class="token punctuation">;</span>


<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> users_link_routes<span class="token punctuation">(</span>
	uid <span class="token keyword">INT</span><span class="token punctuation">,</span>
	rid <span class="token keyword">INT</span><span class="token punctuation">,</span>
	col_time <span class="token keyword">DATETIME</span><span class="token punctuation">,</span>
	
	<span class="token comment">-- 联合主键</span>
	<span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span>uid<span class="token punctuation">,</span>rid<span class="token punctuation">)</span><span class="token punctuation">,</span>
	
	<span class="token keyword">CONSTRAINT</span> l_uid <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span>uid<span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> users<span class="token punctuation">(</span>uid<span class="token punctuation">)</span><span class="token punctuation">,</span>
	<span class="token keyword">CONSTRAINT</span> l_rid <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span><span class="token punctuation">(</span>rid<span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> tours_routes<span class="token punctuation">(</span>rid<span class="token punctuation">)</span>

<span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br><span class="line-number">39</span><br><span class="line-number">40</span><br><span class="line-number">41</span><br><span class="line-number">42</span><br><span class="line-number">43</span><br><span class="line-number">44</span><br><span class="line-number">45</span><br><span class="line-number">46</span><br><span class="line-number">47</span><br><span class="line-number">48</span><br><span class="line-number">49</span><br><span class="line-number">50</span><br><span class="line-number">51</span><br><span class="line-number">52</span><br></div></div><p><img src="https://img-blog.csdnimg.cn/e3ee7b569ef245cd85df31c72f236a1d.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <h2 id="_8-数据库范式"><a href="#_8-数据库范式" class="header-anchor">#</a> 8.数据库范式</h2> <ol start="2"><li>数据库设计的范式
<ul><li><p>概念：设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求</p> <p>设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。</p></li> <li><p>分类：</p> <ol><li><p><strong>第一范式（1NF）：每一列都是不可分割的原子数据项</strong></p></li> <li><p><strong>第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）</strong></p> <ul><li>几个概念：
<ol><li>函数依赖：A--&gt;B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A
例如：学号--&gt;姓名。  （学号，课程名称） --&gt; 分数</li> <li><strong>完全函数依赖：A--&gt;B， 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。</strong>
例如：（学号，课程名称） --&gt; 分数</li> <li><strong>部分函数依赖：A--&gt;B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。</strong>
例如：（学号，课程名称） -- &gt; 姓名</li> <li><strong>传递函数依赖：A--&gt;B, B -- &gt;C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A</strong>
例如：学号--&gt;系名，系名--&gt;系主任</li> <li><strong>码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码</strong>
例如：该表中码为：（学号，课程名称）
<ul><li>主属性：码属性组中的所有属性</li> <li>非主属性：除过码属性组的属性</li></ul></li></ol></li></ul></li> <li><p>第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（<strong>在2NF基础上消除传递依赖</strong>）</p></li></ol></li></ul></li></ol> <ul><li>图解</li></ul> <p><img src="https://img-blog.csdnimg.cn/aaa5251d5c594b2dad6814b32c2d49c8.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <p><img src="https://img-blog.csdnimg.cn/460fc8a61ee048d1b45933e798d94e08.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <p><img src="https://img-blog.csdnimg.cn/a3321b7796df4ce0b03e08742c4047e7.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <p><img src="https://img-blog.csdnimg.cn/6cb05ae36a8d44df97880519806a0af5.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <h2 id="_9-数据库的备份与还原"><a href="#_9-数据库的备份与还原" class="header-anchor">#</a> 9.数据库的备份与还原</h2> <ol><li>命令行：
<ul><li>语法：
<ul><li>备份： mysqldump -u用户名 -p密码 数据库名称 &gt; 保存的路径</li> <li>还原：
<ol><li>登录数据库</li> <li>创建数据库(之前备份的，如果删除)</li> <li>使用数据库(刚才创建的)</li> <li>执行文件。source 文件路径</li></ol></li></ul></li></ul></li> <li>图形化工具：</li></ol> <p><img src="https://img-blog.csdnimg.cn/678b0e3a40f541d98e21cb512b45af68.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <p><img src="https://img-blog.csdnimg.cn/2c330a44daac44688586b9e63b8b1d21.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p></div></div>  <div class="page-edit"><div class="edit-link"><a href="https://github.com/linxin1123/vuepress-blog/edit/master/docs/02.后端/43.MySql笔记/02.MySQL学习第二天.md" target="_blank" rel="noopener noreferrer">编辑</a> <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></div> <!----> <div class="last-updated"><span class="prefix">上次更新:</span> <span class="time">2023/02/17, 11:29:32</span></div></div> <div class="page-nav-wapper"><div class="page-nav-centre-wrap"><a href="/pages/533d88/" class="page-nav-centre page-nav-centre-prev"><div class="tooltip">mysql学习第一天</div></a> <a href="/pages/774997/" class="page-nav-centre page-nav-centre-next"><div class="tooltip">MySql学习第三天</div></a></div> <div class="page-nav"><p class="inner"><span class="prev">
        ←
        <a href="/pages/533d88/" class="prev">mysql学习第一天</a></span> <span class="next"><a href="/pages/774997/">MySql学习第三天</a>→
      </span></p></div></div></div> <div class="article-list"><div class="article-title"><a href="/archives/" class="iconfont icon-bi">最近更新</a></div> <div class="article-wrapper"><dl><dd>01</dd> <dt><a href="/pages/0b785d/"><div>
            Vuex
            <!----></div></a> <span class="date">03-14</span></dt></dl><dl><dd>02</dd> <dt><a href="/pages/c59cc0/"><div>
            Vue响应式原理
            <!----></div></a> <span class="date">03-14</span></dt></dl><dl><dd>03</dd> <dt><a href="/pages/83cc7a/"><div>
            Vue虚拟DOM-cnblog
            <!----></div></a> <span class="date">03-14</span></dt></dl> <dl><dd></dd> <dt><a href="/archives/" class="more">更多文章&gt;</a></dt></dl></div></div></main></div> <div class="footer"><div class="icons"><a href="3010733382@qq.com" title="发邮件" target="_blank" class="iconfont icon-youjian"></a><a href="https://github.com/linxin1123" title="GitHub" target="_blank" class="iconfont icon-github"></a><a href="https://music.163.com/#/playlist?id=755597173" title="听音乐" target="_blank" class="iconfont icon-erji"></a></div> 
  Theme by
  <a href="https://github.com/xugaoyi/vuepress-theme-vdoing" target="_blank" title="本站主题">Vdoing</a> 
    | Copyright © 2022-2023
    <span>lingxin | <a href="https://github.com/linxin1123/vuepress-blog/blob/master/LICENSE" target="_blank">MIT License</a></span></div> <div class="buttons"><div title="返回顶部" class="button blur go-to-top iconfont icon-fanhuidingbu" style="display:none;"></div> <div title="去评论" class="button blur go-to-comment iconfont icon-pinglun" style="display:none;"></div> <div title="主题模式" class="button blur theme-mode-but iconfont icon-zhuti"><ul class="select-box" style="display:none;"><li class="iconfont icon-zidong">
          跟随系统
        </li><li class="iconfont icon-rijianmoshi">
          浅色模式
        </li><li class="iconfont icon-yejianmoshi">
          深色模式
        </li><li class="iconfont icon-yuedu">
          阅读模式
        </li></ul></div></div> <!----> <!----> <!----></div><div class="global-ui"><div></div></div></div>
    <script src="/assets/js/app.57550e13.js" defer></script><script src="/assets/js/2.f014c35f.js" defer></script><script src="/assets/js/92.1c28035b.js" defer></script>
  </body>
</html>
